﻿using System;
using System.Data;
using System.Web;

namespace CoolChat
{

    public class ExcelExporter
    {

        #region Private variables

        private HttpContext page;

        #endregion

        public ExcelExporter(HttpContext TargetPage)
        {
            this.page = TargetPage;
        }

        public void Export(DataSet Data, string ExcelFileName)
        {

            this.page.Response.Clear();
            this.page.Response.ContentType = "application/vnd.ms-excel";
            this.page.Response.AddHeader("Content-Disposition", "attachment; Filename=" + ExcelFileName);

            //頭
            WriteWorkbookHeader();
            //
            foreach (DataTable table in Data.Tables)
                WriteTable(table);
            //尾
            WriteWorkbookFooter();
            this.page.Response.End();

        }

        private void WriteWorkbookHeader()
        {
            this.page.Response.Write("<?xml version=\"1.0\"?>\r\n");
            this.page.Response.Write("<?mso-application progid=\"Excel.Sheet\"?>\r\n");
            this.page.Response.Write("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
            this.page.Response.Write("xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n");
            this.page.Response.Write("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\n");
            this.page.Response.Write("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
            this.page.Response.Write("xmlns:html=\"http://www.w3.org/TR/REC-html40\">\r\n");
            this.page.Response.Write("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">\r\n");
            this.page.Response.Write("<LastAuthor>MSINC</LastAuthor>\r\n");
            this.page.Response.Write("  <Created>" + DateTime.Now.ToString() + "</Created>\r\n");
            this.page.Response.Write("  <Version>11.5703</Version>\r\n");
            this.page.Response.Write("</DocumentProperties>\r\n");
            this.page.Response.Write("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\r\n");
            this.page.Response.Write("  <ProtectStructure>False</ProtectStructure>\r\n");
            this.page.Response.Write("  <ProtectWindows>False</ProtectWindows>\r\n");
            this.page.Response.Write("</ExcelWorkbook>\r\n");
            this.page.Response.Write(" <Styles>\r\n");
            this.page.Response.Write("  <Style ss:ID=\"s1\">\r\n");
            this.page.Response.Write("   <Font ss:Bold=\"1\"/>\r\n");
            this.page.Response.Write("  </Style>\r\n");
            this.page.Response.Write(" </Styles>\r\n");


        }

        private void WriteWorkbookFooter()
        {
            this.page.Response.Write("</Workbook>\r\n");
        }


        private void WriteTable(DataTable table)
        {

            this.page.Response.Write("<Worksheet ss:Name='" + table.TableName + "'>\r\n");
            this.page.Response.Write("<Table ss:ExpandedColumnCount=\"" + table.Columns.Count + "\" ss:ExpandedRowCount=\"" + (table.Rows.Count + 1) + "\" x:FullColumns=\"1\" x:FullRows=\"1\">\r\n");
            WriteTableHeader(table);
            WriteTableRows(table);
            this.page.Response.Write("</Table>\r\n");
            this.page.Response.Write("</Worksheet>\r\n");

        }
        // 表格[頭]
        private void WriteTableHeader(DataTable table)
        {

            foreach (DataColumn column in table.Columns)
                this.page.Response.Write("<Column>" + column.ColumnName + "</Column>\r\n");

            this.page.Response.Write("<Row>\r\n");

            foreach (DataColumn column in table.Columns)
                this.page.Response.Write("<Cell ss:StyleID=\"s1\"><Data ss:Type=\"String\">" + column.ColumnName + "</Data></Cell>\r\n");

            this.page.Response.Write("</Row>\r\n");

        }

        //
        private void WriteTableRows(DataTable table)
        {
            foreach (DataRow Row in table.Rows)
                WriteTableRow(Row);
        }
        //
        private void WriteTableRow(DataRow Row)
        {

            this.page.Response.Write("<Row>\r\n");

            foreach (object loop in Row.ItemArray)
            {

                this.page.Response.Write("<Cell><Data ss:Type=\"" + GetExcelType(loop) + "\">");

                if (loop != null && loop != DBNull.Value)
                {

                    if (loop is byte[])
                        this.page.Response.Write("(...)");
                    else if (loop is decimal)
                    {
                        decimal decimalNumber = (decimal)loop;
                        this.page.Response.Write(System.Web.HttpUtility.HtmlEncode(decimalNumber.ToString("N")));
                    }
                    else if (loop is DateTime)
                    {
                        this.page.Response.Write(((DateTime)loop).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else
                    {
                        this.page.Response.Write(System.Web.HttpUtility.HtmlEncode(loop.ToString()));
                    }

                }

                this.page.Response.Write("</Data></Cell>\r\n");

            }

            this.page.Response.Write("</Row>\r\n");

        }


        private bool IsNumber(string Value)
        {

            if (Value == "")
                return false;

            char[] chars = Value.ToCharArray();

            foreach (char ch in chars)
            {
                if (ch != '$' && ch != '.' && ch != ',' && !char.IsNumber(ch))
                    return false;
            }

            return true;

        }

        private string GetExcelType(object Value)
        {

            if (Value == null || Value == DBNull.Value || Value is string)
                return "String";
            //			else if( Value is DateTime )
            //				return "Date";
            else if (IsNumber(Value.ToString()))
                return "Number";
            else
                return "String";
        }
    }
}